package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Repository;

import com.oocl.dao.CustomerDao;
import com.oocl.pojo.Customer;
import com.oocl.util.DBUtil;

@Repository("customerDao")
public class CustomerDaoImpl implements CustomerDao{
	@Resource(name="dataSource")
	private DataSource dataSource;

	@Override
	public Customer addCustomer(Customer customer) {
		if(null==customer){
			return null;
		}
		Connection connection = null;
		PreparedStatement ps = null;
		String sql = "insert into customers(id,name,sex,sal,birth) values(?,?,?,?,?)";
		int m=0;
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, customer.getId());
			ps.setString(2, customer.getName());
			ps.setBoolean(3, customer.isSex());
			ps.setDouble(4, customer.getSalary());
			ps.setDate(5, new Date(customer.getBirth().getTime()));
			m=ps.executeUpdate();
			if(m>0){
				return customer;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return null;
	}

	@Override
	public Customer updateCustomer(Customer customer) {
		Connection connection = null;
		PreparedStatement ps = null;
		String sql = "update customers set name=?,sex=?,sal=?,birth=? where id=?";
		int m=0;
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, customer.getName());
			ps.setBoolean(2, customer.isSex());
			ps.setDouble(3, customer.getSalary());
			ps.setDate(4, new Date(customer.getBirth().getTime()));
			ps.setString(5, customer.getId());
			m=ps.executeUpdate();
			if(m>0){
				return customer;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return null;
	}

	@Override
	public Customer deleteCustomerById(String id) {
		Connection connection = null;
		PreparedStatement ps = null;
		String sql = "delete from customers where id=?";
		int m=0;
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, id);
			m=ps.executeUpdate();
			if(m>0){
				return new Customer(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return null;
	}

	@Override
	public List<Customer> findAll() {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Customer> customers = new ArrayList<>();
		Customer customer = null;
		String sql = "select * from customers";
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer();
				customer.setId(rs.getString(1));
				customer.setName(rs.getString(2));
				customer.setSex(rs.getBoolean(3));
				customer.setSalary(rs.getDouble(4));
				customer.setBirth(new java.util.Date(rs.getDate(5).getTime()));
				customers.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(rs, ps, connection);
		}
		return customers;
	}

	@Override
	public Customer findCustomerById(String id) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Customer customer = null;
		String sql = "select * from customers where id=?";
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer();
				customer.setId(rs.getString(1));
				customer.setName(rs.getString(2));
				customer.setSex(rs.getBoolean(3));
				customer.setSalary(rs.getDouble(4));
				customer.setBirth(new java.util.Date(rs.getDate(5).getTime()));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(rs, ps, connection);
		}
		return customer;
	}

}
